package com.eastdigit.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;

import java.io.OutputStream;

/**
 * Excel数据写入工具类，POI实现，兼容Excel2003，及Excel2007
 **/

public class ExcelWrite {
    
    /**
     * 写入Excel2007
     * 
     */
    public static void WriteExcel_To2007(String title, String[][] head, String[][] body, OutputStream os) {

        XSSFWorkbook xssf_w_book = new XSSFWorkbook();
        XSSFSheet xssf_w_sheet = xssf_w_book.createSheet(title);
        XSSFRow xssf_w_row = null;// 创建一行
        XSSFCell xssf_w_cell = null;// 创建每个单元格
        int col_count = 0;
        int row_count = 0;
        row_count = body.length;
        col_count = head.length;

        XSSFCellStyle head_cellStyle = xssf_w_book.createCellStyle();// 创建一个单元格样式
        XSSFFont head_font = xssf_w_book.createFont();
        head_font.setFontName("宋体");// 设置头部字体为宋体
        head_font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
        head_font.setFontHeightInPoints((short) 12);
        head_cellStyle.setFont(head_font);// 单元格样式使用字体

        int titleRows = 0; // 标题占据的行数
        xssf_w_row = xssf_w_sheet.createRow(0 + titleRows);// 第一行写入标题行
        for (int i = 0; i < col_count; i++) {
            xssf_w_cell = xssf_w_row.createCell((short) i);

            String type = head[i][2];

            if (type == null) {
                type = "";
            } else if (type.equals("DATE")) {
                XSSFDataFormat head_format = xssf_w_book.createDataFormat();
                head_cellStyle.setDataFormat(head_format.getFormat("m/d/yy"));
                xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                XSSFRichTextString xssfString = new XSSFRichTextString(head[i][0]);
                xssf_w_cell.setCellValue(xssfString);
                xssf_w_cell.setCellStyle(head_cellStyle);
                xssf_w_sheet.autoSizeColumn((short) i);
            } else {
                XSSFDataFormat head_format = xssf_w_book.createDataFormat();
                head_cellStyle.setDataFormat(head_format.getFormat("@"));
                xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                XSSFRichTextString xssfString = new XSSFRichTextString(head[i][0]);
                xssf_w_cell.setCellValue(xssfString);
                xssf_w_cell.setCellStyle(head_cellStyle);
                xssf_w_sheet.autoSizeColumn((short) i);
            }
        }

        XSSFCellStyle cellStyle_CN = xssf_w_book.createCellStyle();// 创建数据单元格样式(数据库数据样式)

        cellStyle_CN.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle_CN.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle_CN.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle_CN.setBorderTop(XSSFCellStyle.BORDER_THIN);

        for (int i = 1; i <= row_count; i++) {
            xssf_w_row = xssf_w_sheet.createRow(i + titleRows);

            for (int j = 0; j < col_count; j++) {
                String val = head[j][1];
                xssf_w_cell = xssf_w_row.createCell((short) j);
                if (val == null || val.trim().equals(""))
                    val = "0";

                String type = head[j][2];

                if (type == null)
                    type = "";
                // 判断类型为数字类型
                else if (type.equals("NUM")) {
                    xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    String value = body[i - 1][Integer.parseInt(val)];
                    if (value == null || value.trim().equals(""))
                        value = "0";
                    xssf_w_cell.setCellValue(Double.parseDouble(value));
                }
                // 判断类型为日期类型
                else if (type.equals("DATE")) {
                    xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);// 设置单元格为字符串类型
                    XSSFDataFormat format = xssf_w_book.createDataFormat();
                    cellStyle_CN.setDataFormat(format.getFormat("yyyy/MM/dd"));
                    String value = body[i - 1][Integer.parseInt(val)];
                    XSSFRichTextString xssfString = new XSSFRichTextString(value);
                    xssf_w_cell.setCellStyle(cellStyle_CN);
                    xssf_w_cell.setCellValue(xssfString);

                } else {
                    xssf_w_cell.setCellType(XSSFCell.CELL_TYPE_STRING);// 设置单元格为字符串类型

                    String value = body[i - 1][Integer.parseInt(val)];
                    if (value == null)
                        value = "";
                    XSSFDataFormat format = xssf_w_book.createDataFormat();
                    cellStyle_CN.setDataFormat(format.getFormat("@"));
                    xssf_w_cell.setCellStyle(cellStyle_CN);
                    XSSFRichTextString xssfString = new XSSFRichTextString(value);
                    xssf_w_cell.setCellValue(xssfString);
                }

            }

        }

        try {
            xssf_w_book.write(os);
            os.flush();
            os.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    
    /**
     * 写入Excel2003 兼容模式
     * 
     */
    public static void WriteExcel_To2003(String title, String[][] head, String[][] body, OutputStream os) {
        HSSFWorkbook hssf_w_wk = new HSSFWorkbook();// 创建excel
        HSSFSheet hssf_w_s = hssf_w_wk.createSheet(title);// 创建一个工作空间
        HSSFRow hssf_w_r = null;// 创建一行
        HSSFCell hssf_w_c = null;// 每个单元格
        // 填写内容
        int col_count = 0;
        int row_count = 0;
        row_count = body.length;
        col_count = head.length;

        HSSFCellStyle cellStyle = hssf_w_wk.createCellStyle();
        HSSFFont font = hssf_w_wk.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");// 设置标题字体
        cellStyle.setFont(font);

        // 标题
        int titleRows = 0; // 标题占据的行数
        hssf_w_r = hssf_w_s.createRow(0);
        // 第一行写入标题行
        hssf_w_r = hssf_w_s.createRow(0 + titleRows);

        cellStyle = hssf_w_wk.createCellStyle();
        cellStyle.setFont(font);// 设置列标题样式
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < col_count; i++) {
            hssf_w_c = hssf_w_r.createCell((short) i);
            String type = head[i][2];
            if (type == null) {
                type = "";
            } else if (type.equals("DATE")) {
                HSSFDataFormat format1 = hssf_w_wk.createDataFormat();
                cellStyle.setDataFormat(format1.getFormat("m/d/yy"));
                hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString hssfString = new HSSFRichTextString(head[i][0]);
                hssf_w_c.setCellValue(hssfString);
                hssf_w_c.setCellStyle(cellStyle);
                hssf_w_s.autoSizeColumn((short) i);
            } else {
                HSSFDataFormat format = hssf_w_wk.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));
                hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString hssfString = new HSSFRichTextString(head[i][0]);
                hssf_w_c.setCellValue(hssfString);
                hssf_w_c.setCellStyle(cellStyle);
                hssf_w_s.autoSizeColumn((short) i);
            }
        }
        // 第二行开始写入数据

        cellStyle = hssf_w_wk.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        for (int i = 1; i <= row_count; i++) {
            hssf_w_r = hssf_w_s.createRow(i + titleRows);

            for (int j = 0; j < col_count; j++) {
                String val = head[j][1];
                hssf_w_c = hssf_w_r.createCell((short) j);
                if (val == null || val.trim().equals(""))
                    val = "0";

                String type = head[j][2];

                if (type == null)
                    type = "";
                // 判断类型为数字类型
                else if (type.equals("NUM")) {
                    hssf_w_c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    String value = body[i - 1][Integer.parseInt(val)];
                    if (value == null || value.trim().equals(""))
                        value = "0";
                    hssf_w_c.setCellValue(Double.parseDouble(value));
                }
                // 判断类型为日期类型
                else if (type.equals("DATE")) {
                    HSSFDataFormat format1 = hssf_w_wk.createDataFormat();
                    cellStyle.setDataFormat(format1.getFormat("yyyy/m/d/"));
                    String value = body[i - 1][Integer.parseInt(val)];
                    HSSFRichTextString hssfString = new HSSFRichTextString(value);
                    hssf_w_c.setCellStyle(cellStyle);
                    hssf_w_c.setCellValue(hssfString);
                } else {
                    hssf_w_c.setCellType(HSSFCell.CELL_TYPE_STRING);
                    String value = body[i - 1][Integer.parseInt(val)];
                    if (value == null)
                        value = "";
                    HSSFDataFormat format2 = hssf_w_wk.createDataFormat();
                    cellStyle.setDataFormat(format2.getFormat("@"));
                    hssf_w_c.setCellStyle(cellStyle);
                    HSSFRichTextString hssfString = new HSSFRichTextString(value);
                    hssf_w_c.setCellValue(hssfString);
                }
            }
        }
        try {
            hssf_w_wk.write(os);
            os.flush();
            os.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub

    }

}
